package com.fbty.mt.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.fbty.mt.domain.ErrorIpInfo;
import com.fbty.mt.domain.IpInfo;
import com.fbty.mt.domain.OnlyIpInfo;
import com.fbty.mt.service.IpService;

public class ExcelTool {
	private static FileInputStream fs;
	private static HSSFWorkbook wb;
	private static HSSFSheet sheet;
	private static HSSFRow row;
    private static XSSFWorkbook xwb;
    private static Integer rowNum;
    private static Integer sheets;
    private IpService ipInfoService;
	/**
	* 解析共享类ip  excel文件
	* n 从第几行开始读取数据
	**/
	public static List<IpInfo> readExcelContent(File file, int n) {
		List<IpInfo> list = new ArrayList<IpInfo>();        
		try {
			fs = new FileInputStream(file);
			wb = new HSSFWorkbook(new POIFSFileSystem(fs));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		//得到表的个数
		int sheets=wb.getNumberOfSheets();
		
     //  
		sheet = wb.getSheetAt(0);
		// 得到总行数
	    rowNum = sheet.getLastRowNum();
		// 得到第一行
		row = sheet.getRow(0);
		// 得到每行总单元格数
		int colNum = row.getPhysicalNumberOfCells();		
		// 正文内容应该从第n行开始
		for (int i = n - 1; i <= rowNum; i++) {
				int h=0;
				IpInfo ipInfo = new IpInfo();
				ipInfo.setUpdateTime(new Date());
				HSSFSheet sheetn=wb.getSheetAt(0);
				if( sheetn.getRow(i)!=null){
					HSSFRow rown = sheetn.getRow(i);				
					int colNumn=rown.getPhysicalNumberOfCells();
					while (h < colNumn) {
						String str = getCellFormatValue(rown.getCell(h)).trim();
						if(h == 1) {
							ipInfo.setIp(str);
						} else if(h == 2) {
							ipInfo.setPort(str);
						} else if(h == 3){
							ipInfo.setType(str);
						}
						h++;
					}
					IpTxt ipTxt = new IpTxt();
					String regex = "^(1\\d{2}|2[0-4]\\d|25[0-5]|[1-9]\\d|[1-9])\\."                  
						+ "(1\\d{2}|2[0-4]\\d|25[0-5]|[1-9]\\d|\\d)\\."                  
						+ "(1\\d{2}|2[0-4]\\d|25[0-5]|[1-9]\\d|\\d)\\."                  
						+ "(1\\d{2}|2[0-4]\\d|25[0-5]|[1-9]\\d|\\d)$"; 
					if(ipInfo.getIp().matches(regex)){
						ipTxt.setIp(ipInfo.getIp());
						if(ipInfo.getPort() != null && !"".equals(ipInfo.getPort()) && Double.valueOf(ipInfo.getPort()).intValue() > 0) {
							ipTxt.setPort(Double.valueOf(ipInfo.getPort()).intValue());
							if(ipInfo.getType() != null && !"".equals(ipInfo.getType())) {
								ipTxt.setType(ipInfo.getType());
								//存入缓存
								StaticValue.uploadTxt.add(ipTxt);
							} else {
								errorIp(ipInfo);
							}
						} else {
							errorIp(ipInfo);
						}
					} else {
						errorIp(ipInfo);
					}
				}
		}
		return list;
	}

	public static void errorIp(IpInfo ipInfo) {
		ErrorIpInfo errorIpInfo = new ErrorIpInfo();
		errorIpInfo.setIp(ipInfo.getIp());
		errorIpInfo.setPort(ipInfo.getPort());
		errorIpInfo.setType(ipInfo.getType());
		errorIpInfo.setUploadTime(new Date());
		errorIpInfo.setState(1);
		//存入缓存
		StaticValue.errorIpList.add(errorIpInfo);
	}
	/**
	* 解析独享类ip  excel文件
	* n 从第几行开始读取数据
	**/
	public static List<OnlyIpInfo> readOnlyExcelContent(File file, int n) {
		List<OnlyIpInfo> list = new ArrayList<OnlyIpInfo>();        
        
		try {
			fs = new FileInputStream(file);
			
			wb = new HSSFWorkbook(new POIFSFileSystem(fs));
			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		//得到表的个数
		int sheets=wb.getNumberOfSheets();
		
     //  
		sheet = wb.getSheetAt(0);
		// 得到总行数
	    rowNum = sheet.getLastRowNum();
		// 得到第一行
		row = sheet.getRow(0);
		// 得到每行总单元格数
		int colNum = row.getPhysicalNumberOfCells();		
		// 正文内容应该从第n行开始
		for (int i = n - 1; i <= rowNum; i++) {
				int h=0;
				OnlyIpInfo ipInfo = new OnlyIpInfo();
				HSSFSheet sheetn=wb.getSheetAt(0);
				if( sheetn.getRow(i)!=null){
					HSSFRow rown = sheetn.getRow(i);				
					int colNumn=rown.getPhysicalNumberOfCells();
					while (h < colNumn) {
						String str = getCellFormatValue(rown.getCell(h)).trim();
						h++;
					}
				}
				list.add(ipInfo);
			
		}
		return list;
	}

	public static Integer getSheets() {
		return sheets;
	}

	public static void setSheets(Integer sheets) {
		ExcelTool.sheets = sheets;
	}

	private static String getCellFormatValue(HSSFCell cell) {
		String cellvalue = "";
		if (cell != null) {
			// 判断当前Cell的Type
			switch (cell.getCellType()) {
			// 如果当前Cell的Type为NUMERIC
			case HSSFCell.CELL_TYPE_NUMERIC:
			case HSSFCell.CELL_TYPE_FORMULA: {
				// 判断当前的cell是否为Date
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					// 如果是Date类型则，转化为Data格式
					// 方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
					// cellvalue = cell.getDateCellValue().toLocaleString();
					// 方法2：这样子的data格式是不带带时分秒的：2011-10-12
					Date date = cell.getDateCellValue();
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					cellvalue = sdf.format(date);
				}
				// 如果是纯数字
				else {
					// 取得当前Cell的数值
					cellvalue = String.valueOf(cell.getNumericCellValue());
				}
				break;
			}
			// 如果当前Cell的Type为STRIN
			case HSSFCell.CELL_TYPE_STRING:
				// 取得当前的Cell字符串
				cellvalue = cell.getRichStringCellValue().getString();
				break;
			// 默认的Cell值
			default:
				cellvalue = " ";
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;
	}

	public static void main(String[] args) {
		System.out.println(StaticValue.uploadTxt);
//		try {
//			String path = "D:\\123.xls";
//			//new File(path) 表示哪个Excel表             15表示在这个表中从哪一行开始读
//			List<Map<Integer, String>> list = readExcelContent(new File(path),1);
//			for (Map<Integer, String> map : list) {
//				if(map.get(0)==null || "".equals(map.get(0))){
//					return;
//				}
//				for (int i = 0; i < map.size(); i++) {
//					System.out.print(map.get(i)+" ");
//				}
//				System.out.println();
//				System.out.println("--------------------------");
//			}
//		} catch (Exception e) {
//			System.out.println("没有找到文件！");
//		}
	}

	public static XSSFWorkbook getXwb() {
		return xwb;
	}


	public static Integer getRowNum() {
		return rowNum;
	}


	public IpService getIpInfoService() {
		return ipInfoService;
	}


	public void setIpInfoService(IpService ipInfoService) {
		this.ipInfoService = ipInfoService;
	}


}
